﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class FuelMachinas2Object
    {
        public const string sql = @"
SELECT	convert(nchar(20),DictionaryMachines.Name) as Машина,
		convert(nchar(20),COALESCE(FirstLastFuelRemaining.FirstFuelRemaining,0)) as Первый__ост_в_баке,
		convert(nchar(20),COALESCE(total.Liters,0)) as Заправлено__л,
		convert(nchar(20),COALESCE(FirstLastFuelRemaining.LastFuelRemaining,0)) as Посл__ост_в_баке,
		convert(nchar(20),COALESCE(total.Distance,0)) as Пробег__км,
		convert(nchar(20),COALESCE(Round((((FirstLastFuelRemaining.FirstFuelRemaining + total.Liters - FirstLastFuelRemaining.LastFuelRemaining) * 100) / total.Distance),2),0)) as Расход,
		'(Первый остаток в баке + Всего заправлено - Последний остаток в баке) х100 / Всего пробег = Расход (л/100 км)' as Формула
FROM (
	SELECT Machine
		  ,sum(r.Liters) as Liters
		  ,sum(r.Distance) as Distance 
  	FROM Refueling as r 
  	WHERE r.FuelOperType = 2 
  	GROUP BY Machine) AS total
LEFT JOIN DictionaryMachines on total.Machine = DictionaryMachines.ID
LEFT JOIN (
select idr.Machine as Machine, Refueling_min.FuelRemaining as FirstFuelRemaining, Refueling_max.FuelRemaining as LastFuelRemaining
From (SELECT MinID.machine, MinID.min_id, MaxID.max_id
FROM (	SELECT r2.machine as machine, min (r2.id) as min_id
		FROM (SELECT id, Date, machine FROM Refueling) as r2
					lEFT JOIN 
						(SELECT r1.machine, min (r1.date) as min_date, max (r1.date) as max_date
						FROM (SELECT id, Date, machine FROM Refueling) as r1
						GROUP BY r1.machine) as MinMaxDate on r2.machine = MinMaxDate.machine
		Where r2.Date = MinMaxDate.min_date
		GROUP BY r2.machine) as MinID
LEFT JOIN (	SELECT r2.machine, max (r2.id) as max_id
			FROM (SELECT id, Date, machine FROM Refueling) as r2
						lEFT JOIN 
							(SELECT r1.machine, min (r1.date) as min_date, max (r1.date) as max_date
							FROM (SELECT id, Date, machine FROM Refueling) as r1
							GROUP BY r1.machine) as MinMaxDate on r2.machine = MinMaxDate.machine
			Where r2.Date = MinMaxDate.max_date
			GROUP BY r2.machine) as MaxID
ON 	MinID.machine = MaxID.machine) as idr
LEFT JOIN Refueling as Refueling_min on idr.min_id = Refueling_min.id
LEFT JOIN Refueling as Refueling_max on idr.max_id = Refueling_max.id) as FirstLastFuelRemaining on total.Machine = FirstLastFuelRemaining.Machine
";
        public string Машина { get; set; }
        public string Первый__ост_в_баке { get; set; }
        public string Заправлено__л { get; set; }
        public string Посл__ост_в_баке { get; set; }
        public string Пробег__км { get; set; }
        public string Расход { get; set; }
        public string Формула { get; set; }
    }
}
